Analysis with SQL.ipynb

{"cells": [{"metadata": {}, "id": "22caaa44-c27e-4d29-b5ee-49dca4cc7cf3", "cell_type": "markdown", "source": "<p style=\"text-align:center\">\n    <a href=\"https://skills.network/?utm_medium=Exinfluencer&utm_source=Exinfluencer&utm_content=000026UJ&utm_term=10006555&utm_id=NA-SkillsNetwork-Channel-SkillsNetworkCoursesIBMDS0321ENSkillsNetwork865-2022-01-01\" target=\"_blank\">\n    <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/assets/logos/SN_web_lightmode.png\" width=\"200\" alt=\"Skills Network Logo\"  />\n    </a>\n</p>\n\n<h1 align=center><font size = 5>Assignment: SQL Notebook for Peer Assignment</font></h1>\n\nEstimated time needed: **60** minutes.\n\n## Introduction\nUsing this Python notebook you will:\n\n1.  Understand the Spacex DataSet\n2.  Load the dataset  into the corresponding table in a Db2 database\n3.  Execute SQL queries to answer assignment questions \n"}, {"metadata": {}, "id": "1ae8e773-a2d5-4721-b534-598518e7c7e7", "cell_type": "markdown", "source": "## Overview of the DataSet\n\nSpaceX has gained worldwide attention for a series of historic milestones. \n\nIt is the only private company ever to return a spacecraft from low-earth orbit, which it first accomplished in December 2010.\nSpaceX advertises Falcon 9 rocket launches on its website with a cost of 62 million dollars wheras other providers cost upward of 165 million dollars each, much of the savings is because Space X can reuse the first stage. \n\n\nTherefore if we can determine if the first stage will land, we can determine the cost of a launch. \n\nThis information can be used if an alternate company wants to bid against SpaceX for a rocket launch.\n\nThis dataset includes a record for each payload carried during a SpaceX mission into outer space.\n"}, {"metadata": {}, "id": "8a3770ec-da5d-4ee2-b913-12094cd3dfb0", "cell_type": "markdown", "source": "### Download the datasets\n\nThis assignment requires you to load the spacex dataset.\n\nIn many cases the dataset to be analyzed is available as a .CSV (comma separated values) file, perhaps on the internet. Click on the link below to download and save the dataset (.CSV file):\n\n <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/data/Spacex.csv\" target=\"_blank\">Spacex DataSet</a>\n\n"}, {"metadata": {}, "id": "99833862-9852-410a-9211-b655a30013b0", "cell_type": "markdown", "source": "**Navigate to the Go to UI screen** \n\n* Refer to this insruction in this <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Sign%20up%20for%20IBM%20Cloud%20-%20Create%20Db2%20service%20instance%20-%20Get%20started%20with%20the%20Db2%20console/instructional-labs.md.html\">link</a> for viewing  the   Go to UI screen. \n\n\n* Later click on **Data link(below SQL)**  in the Go to UI screen  and click on **Load Data** tab.  \n\n\n\n* Later browse for the downloaded spacex file.\n\n\n\n<img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/browsefile.png\" width=\"800\">\n\n\n* Once done select the schema andload the file.  \n\n\n <img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/spacexload3.png\" width=\"800\">\n \n"}, {"metadata": {}, "id": "f76e14f8-fa6a-445f-b2cc-7a4cf400a353", "cell_type": "markdown", "source": "\nIf you are facing a problem in uploading the dataset (which is a csv file), you can follow the steps below to upload the .sql file instead of the CSV file:\n\n* Download the file <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/datasets/Spacex%20.sql\">Spacex.sql</a>\n\n* Later click on **SQL** in the  **Go to UI Screen**.\n\n* Use the **From file** option to browse for the **SQL** file and upload it.\n\n<img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/sqlfile.png\">\n\n* Once you upload the script,you can use the **Run All** option to run all the queries to insert the data.\n\n<img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/runall.png\">\n\n    \n"}, {"metadata": {}, "id": "80dbd24e-b3c4-456b-a0a0-e6b252d5172f", "cell_type": "code", "source": "!pip install --force-reinstall ibm_db==3.1.0 ibm_db_sa==0.3.3\n# Ensure we don't load_ext with sqlalchemy>=1.4 (incompadible)\n!pip uninstall sqlalchemy==1.4 -y && pip install sqlalchemy==2.0.4\n!pip install ipython-sql", "execution_count": 49, "outputs": [{"output_type": "stream", "text": "Collecting ibm_db==3.1.0\n  Using cached ibm_db-3.1.0-cp310-cp310-linux_x86_64.whl\nCollecting ibm_db_sa==0.3.3\n  Using cached ibm_db_sa-0.3.3-py3-none-any.whl\nCollecting sqlalchemy>=0.7.3\n  Using cached SQLAlchemy-2.0.4-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (2.7 MB)\nCollecting greenlet!=0.4.17\n  Using cached greenlet-2.0.2-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (613 kB)\nCollecting typing-extensions>=4.2.0\n  Using cached typing_extensions-4.5.0-py3-none-any.whl (27 kB)\nInstalling collected packages: ibm_db, typing-extensions, greenlet, sqlalchemy, ibm_db_sa\n  Attempting uninstall: ibm_db\n    Found existing installation: ibm-db 3.1.0\n    Uninstalling ibm-db-3.1.0:\n      Successfully uninstalled ibm-db-3.1.0\n  Attempting uninstall: typing-extensions\n    Found existing installation: typing_extensions 4.5.0\n    Uninstalling typing_extensions-4.5.0:\n      Successfully uninstalled typing_extensions-4.5.0\n  Attempting uninstall: greenlet\n    Found existing installation: greenlet 2.0.2\n    Uninstalling greenlet-2.0.2:\n      Successfully uninstalled greenlet-2.0.2\n  Attempting uninstall: sqlalchemy\n    Found existing installation: SQLAlchemy 2.0.4\n    Uninstalling SQLAlchemy-2.0.4:\n      Successfully uninstalled SQLAlchemy-2.0.4\n  Attempting uninstall: ibm_db_sa\n    Found existing installation: ibm-db-sa 0.3.3\n    Uninstalling ibm-db-sa-0.3.3:\n      Successfully uninstalled ibm-db-sa-0.3.3\nSuccessfully installed greenlet-2.0.2 ibm_db-3.1.0 ibm_db_sa-0.3.3 sqlalchemy-2.0.4 typing-extensions-4.5.0\nFound existing installation: SQLAlchemy 2.0.4\nUninstalling SQLAlchemy-2.0.4:\n  Successfully uninstalled SQLAlchemy-2.0.4\nCollecting sqlalchemy==2.0.4\n  Using cached SQLAlchemy-2.0.4-cp310-cp310-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (2.7 MB)\nRequirement already satisfied: greenlet!=0.4.17 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from sqlalchemy==2.0.4) (2.0.2)\nRequirement already satisfied: typing-extensions>=4.2.0 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from sqlalchemy==2.0.4) (4.5.0)\nInstalling collected packages: sqlalchemy\nSuccessfully installed sqlalchemy-2.0.4\nRequirement already satisfied: ipython-sql in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (0.5.0)\nRequirement already satisfied: ipython-genutils in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql) (0.2.0)\nRequirement already satisfied: ipython in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql) (8.4.0)\nRequirement already satisfied: prettytable in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql) (3.6.0)\nRequirement already satisfied: six in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql) (1.16.0)\nRequirement already satisfied: sqlalchemy>=2.0 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql) (2.0.4)\nRequirement already satisfied: sqlparse in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython-sql) (0.4.3)\nRequirement already satisfied: typing-extensions>=4.2.0 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from sqlalchemy>=2.0->ipython-sql) (4.5.0)\nRequirement already satisfied: greenlet!=0.4.17 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from sqlalchemy>=2.0->ipython-sql) (2.0.2)\nRequirement already satisfied: decorator in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython->ipython-sql) (5.1.1)\nRequirement already satisfied: setuptools>=18.5 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython->ipython-sql) (65.6.3)\nRequirement already satisfied: pickleshare in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython->ipython-sql) (0.7.5)\nRequirement already satisfied: matplotlib-inline in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython->ipython-sql) (0.1.6)\nRequirement already satisfied: pygments>=2.4.0 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython->ipython-sql) (2.11.2)\nRequirement already satisfied: stack-data in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython->ipython-sql) (0.2.0)\nRequirement already satisfied: backcall in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython->ipython-sql) (0.2.0)\nRequirement already satisfied: prompt-toolkit!=3.0.0,!=3.0.1,<3.1.0,>=2.0.0 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython->ipython-sql) (3.0.20)\nRequirement already satisfied: pexpect>4.3 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython->ipython-sql) (4.8.0)\nRequirement already satisfied: jedi>=0.16 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython->ipython-sql) (0.18.1)\nRequirement already satisfied: traitlets>=5 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from ipython->ipython-sql) (5.1.1)\nRequirement already satisfied: wcwidth in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from prettytable->ipython-sql) (0.2.5)\nRequirement already satisfied: parso<0.9.0,>=0.8.0 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from jedi>=0.16->ipython->ipython-sql) (0.8.3)\nRequirement already satisfied: ptyprocess>=0.5 in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from pexpect>4.3->ipython->ipython-sql) (0.7.0)\nRequirement already satisfied: asttokens in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from stack-data->ipython->ipython-sql) (2.0.5)\nRequirement already satisfied: executing in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from stack-data->ipython->ipython-sql) (0.8.3)\nRequirement already satisfied: pure-eval in /opt/conda/envs/Python-3.10/lib/python3.10/site-packages (from stack-data->ipython->ipython-sql) (0.2.2)\n", "name": "stdout"}]}, {"metadata": {}, "cell_type": "code", "source": "", "execution_count": null, "outputs": []}, {"metadata": {}, "id": "4d078618-5f50-48d9-9b65-20da01d2228c", "cell_type": "markdown", "source": "### Connect to the database\n\nLet us first load the SQL extension and establish a connection with the database\n"}, {"metadata": {}, "id": "dbecf5cd-c2d8-4b82-b84a-244c41e72af4", "cell_type": "code", "source": "%load_ext sql", "execution_count": 50, "outputs": [{"output_type": "stream", "text": "The sql extension is already loaded. To reload it, use:\n  %reload_ext sql\n", "name": "stdout"}]}, {"metadata": {}, "id": "b6599c7d-66c8-4569-ac9e-9f57523a8cc1", "cell_type": "markdown", "source": "\n\n\n**DB2 magic in case of  UI service credentials.**\n\n\n\n<img src=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBM-DS0321EN-SkillsNetwork/labs/module_2/images/servicecredentials.png\" width=\"600\">  \n\n* Use the following format.\n\n* Add security=SSL at the end\n\n**%sql ibm_db_sa://my-username:my-password@my-hostname:my-port/my-db-name?security=SSL**\n"}, {"metadata": {}, "id": "b431bf93-ccdc-4c4b-8ae8-9b7528861f1f", "cell_type": "code", "source": "", "execution_count": null, "outputs": []}, {"metadata": {}, "id": "e5a21bec-6e50-4320-aa90-4d46ccc235bf", "cell_type": "code", "source": "%sql db2+ibm_db://yhq87479:YcJIf2NJ6EFX1hb8@8e359033-a1c9-4643-82ef-8ac06f5107eb.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30120/bludb?security=SSL", "execution_count": 51, "outputs": [{"output_type": "stream", "text": "Traceback (most recent call last):\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sql/connection.py\", line 45, in __init__\n    engine = sqlalchemy.create_engine(\n  File \"<string>\", line 2, in create_engine\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py\", line 277, in warned\n    return fn(*args, **kwargs)  # type: ignore[no-any-return]\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/engine/create.py\", line 556, in create_engine\n    entrypoint = u._get_entrypoint()\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/engine/url.py\", line 754, in _get_entrypoint\n    cls = registry.load(name)\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py\", line 352, in load\n    return self.impls[name]()\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/importlib/metadata/__init__.py\", line 171, in load\n    module = import_module(match.group('module'))\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/importlib/__init__.py\", line 126, in import_module\n    return _bootstrap._gcd_import(name[level:], package, level)\n  File \"<frozen importlib._bootstrap>\", line 1050, in _gcd_import\n  File \"<frozen importlib._bootstrap>\", line 1027, in _find_and_load\n  File \"<frozen importlib._bootstrap>\", line 992, in _find_and_load_unlocked\n  File \"<frozen importlib._bootstrap>\", line 241, in _call_with_frames_removed\n  File \"<frozen importlib._bootstrap>\", line 1050, in _gcd_import\n  File \"<frozen importlib._bootstrap>\", line 1027, in _find_and_load\n  File \"<frozen importlib._bootstrap>\", line 1006, in _find_and_load_unlocked\n  File \"<frozen importlib._bootstrap>\", line 688, in _load_unlocked\n  File \"<frozen importlib._bootstrap_external>\", line 883, in exec_module\n  File \"<frozen importlib._bootstrap>\", line 241, in _call_with_frames_removed\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/ibm_db_sa/__init__.py\", line 22, in <module>\n    from . import ibm_db, pyodbc, base, zxjdbc\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/ibm_db_sa/ibm_db.py\", line 21, in <module>\n    from sqlalchemy import processors, types as sa_types, util\nImportError: cannot import name 'processors' from 'sqlalchemy' (/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/__init__.py)\n\nConnection info needed in SQLAlchemy format, example:\n               postgresql://username:password@hostname/dbname\n               or an existing connection: dict_keys([])\nTraceback (most recent call last):\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sql/magic.py\", line 196, in execute\n    conn = sql.connection.Connection.set(\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sql/connection.py\", line 70, in set\n    cls.current = existing or Connection(descriptor, connect_args, creator)\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sql/connection.py\", line 45, in __init__\n    engine = sqlalchemy.create_engine(\n  File \"<string>\", line 2, in create_engine\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py\", line 277, in warned\n    return fn(*args, **kwargs)  # type: ignore[no-any-return]\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/engine/create.py\", line 556, in create_engine\n    entrypoint = u._get_entrypoint()\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/engine/url.py\", line 754, in _get_entrypoint\n    cls = registry.load(name)\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py\", line 352, in load\n    return self.impls[name]()\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/importlib/metadata/__init__.py\", line 171, in load\n    module = import_module(match.group('module'))\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/importlib/__init__.py\", line 126, in import_module\n    return _bootstrap._gcd_import(name[level:], package, level)\n  File \"<frozen importlib._bootstrap>\", line 1050, in _gcd_import\n  File \"<frozen importlib._bootstrap>\", line 1027, in _find_and_load\n  File \"<frozen importlib._bootstrap>\", line 992, in _find_and_load_unlocked\n  File \"<frozen importlib._bootstrap>\", line 241, in _call_with_frames_removed\n  File \"<frozen importlib._bootstrap>\", line 1050, in _gcd_import\n  File \"<frozen importlib._bootstrap>\", line 1027, in _find_and_load\n  File \"<frozen importlib._bootstrap>\", line 1006, in _find_and_load_unlocked\n  File \"<frozen importlib._bootstrap>\", line 688, in _load_unlocked\n  File \"<frozen importlib._bootstrap_external>\", line 883, in exec_module\n  File \"<frozen importlib._bootstrap>\", line 241, in _call_with_frames_removed\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/ibm_db_sa/__init__.py\", line 22, in <module>\n    from . import ibm_db, pyodbc, base, zxjdbc\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/ibm_db_sa/ibm_db.py\", line 21, in <module>\n    from sqlalchemy import processors, types as sa_types, util\nImportError: cannot import name 'processors' from 'sqlalchemy' (/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/__init__.py)\n\nConnection info needed in SQLAlchemy format, example:\n               postgresql://username:password@hostname/dbname\n               or an existing connection: dict_keys([])\n", "name": "stdout"}]}, {"metadata": {}, "id": "3a777815-54d0-492b-addb-78cdc1ee0dd7", "cell_type": "code", "source": "%sql ibm_db_sa:///yhq87479:YcJIf2NJ6EFX1hb8@8e359033-a1c9-4643-82ef-8ac06f5107eb.bs2io90l08kqb1od8lcg.databases.appdomain.cloud:30120/bludb?security=SSL", "execution_count": 53, "outputs": [{"output_type": "stream", "text": "Traceback (most recent call last):\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sql/connection.py\", line 45, in __init__\n    engine = sqlalchemy.create_engine(\n  File \"<string>\", line 2, in create_engine\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py\", line 277, in warned\n    return fn(*args, **kwargs)  # type: ignore[no-any-return]\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/engine/create.py\", line 556, in create_engine\n    entrypoint = u._get_entrypoint()\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/engine/url.py\", line 754, in _get_entrypoint\n    cls = registry.load(name)\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py\", line 352, in load\n    return self.impls[name]()\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/importlib/metadata/__init__.py\", line 171, in load\n    module = import_module(match.group('module'))\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/importlib/__init__.py\", line 126, in import_module\n    return _bootstrap._gcd_import(name[level:], package, level)\n  File \"<frozen importlib._bootstrap>\", line 1050, in _gcd_import\n  File \"<frozen importlib._bootstrap>\", line 1027, in _find_and_load\n  File \"<frozen importlib._bootstrap>\", line 992, in _find_and_load_unlocked\n  File \"<frozen importlib._bootstrap>\", line 241, in _call_with_frames_removed\n  File \"<frozen importlib._bootstrap>\", line 1050, in _gcd_import\n  File \"<frozen importlib._bootstrap>\", line 1027, in _find_and_load\n  File \"<frozen importlib._bootstrap>\", line 1006, in _find_and_load_unlocked\n  File \"<frozen importlib._bootstrap>\", line 688, in _load_unlocked\n  File \"<frozen importlib._bootstrap_external>\", line 883, in exec_module\n  File \"<frozen importlib._bootstrap>\", line 241, in _call_with_frames_removed\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/ibm_db_sa/__init__.py\", line 22, in <module>\n    from . import ibm_db, pyodbc, base, zxjdbc\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/ibm_db_sa/ibm_db.py\", line 21, in <module>\n    from sqlalchemy import processors, types as sa_types, util\nImportError: cannot import name 'processors' from 'sqlalchemy' (/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/__init__.py)\n\nConnection info needed in SQLAlchemy format, example:\n               postgresql://username:password@hostname/dbname\n               or an existing connection: dict_keys([])\nTraceback (most recent call last):\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sql/magic.py\", line 196, in execute\n    conn = sql.connection.Connection.set(\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sql/connection.py\", line 70, in set\n    cls.current = existing or Connection(descriptor, connect_args, creator)\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sql/connection.py\", line 45, in __init__\n    engine = sqlalchemy.create_engine(\n  File \"<string>\", line 2, in create_engine\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py\", line 277, in warned\n    return fn(*args, **kwargs)  # type: ignore[no-any-return]\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/engine/create.py\", line 556, in create_engine\n    entrypoint = u._get_entrypoint()\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/engine/url.py\", line 754, in _get_entrypoint\n    cls = registry.load(name)\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py\", line 352, in load\n    return self.impls[name]()\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/importlib/metadata/__init__.py\", line 171, in load\n    module = import_module(match.group('module'))\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/importlib/__init__.py\", line 126, in import_module\n    return _bootstrap._gcd_import(name[level:], package, level)\n  File \"<frozen importlib._bootstrap>\", line 1050, in _gcd_import\n  File \"<frozen importlib._bootstrap>\", line 1027, in _find_and_load\n  File \"<frozen importlib._bootstrap>\", line 992, in _find_and_load_unlocked\n  File \"<frozen importlib._bootstrap>\", line 241, in _call_with_frames_removed\n  File \"<frozen importlib._bootstrap>\", line 1050, in _gcd_import\n  File \"<frozen importlib._bootstrap>\", line 1027, in _find_and_load\n  File \"<frozen importlib._bootstrap>\", line 1006, in _find_and_load_unlocked\n  File \"<frozen importlib._bootstrap>\", line 688, in _load_unlocked\n  File \"<frozen importlib._bootstrap_external>\", line 883, in exec_module\n  File \"<frozen importlib._bootstrap>\", line 241, in _call_with_frames_removed\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/ibm_db_sa/__init__.py\", line 22, in <module>\n    from . import ibm_db, pyodbc, base, zxjdbc\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/ibm_db_sa/ibm_db.py\", line 21, in <module>\n    from sqlalchemy import processors, types as sa_types, util\nImportError: cannot import name 'processors' from 'sqlalchemy' (/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sqlalchemy/__init__.py)\n\nConnection info needed in SQLAlchemy format, example:\n               postgresql://username:password@hostname/dbname\n               or an existing connection: dict_keys([])\n", "name": "stdout"}]}, {"metadata": {}, "id": "1e99228b-31e0-45ab-af44-5d0a98e2edd5", "cell_type": "markdown", "source": "## Tasks\n\nNow write and execute SQL queries to solve the assignment tasks.\n\n### Task 1\n\n\n\n\n##### Display the names of the unique launch sites  in the space mission\n"}, {"metadata": {}, "id": "36cb2cda-f45b-432a-9f96-333a7d577c75", "cell_type": "code", "source": "%sql select DISTINCT LAUNCH_SITE from spacex;", "execution_count": 30, "outputs": [{"output_type": "stream", "text": "Traceback (most recent call last):\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sql/magic.py\", line 196, in execute\n    conn = sql.connection.Connection.set(\n  File \"/opt/conda/envs/Python-3.10/lib/python3.10/site-packages/sql/connection.py\", line 82, in set\n    raise ConnectionError(\nsql.connection.ConnectionError: Environment variable $DATABASE_URL not set, and no connect string given.\n\nConnection info needed in SQLAlchemy format, example:\n               postgresql://username:password@hostname/dbname\n               or an existing connection: dict_keys([])\n", "name": "stdout"}]}, {"metadata": {}, "id": "0bade2d8-a5cf-4325-8419-8bcd03f4efd1", "cell_type": "markdown", "source": "\n### Task 2\n\n\n#####  Display 5 records where launch sites begin with the string 'CCA' \n"}, {"metadata": {}, "id": "5f4740b9-7e45-495a-8aec-108d52124302", "cell_type": "code", "source": "%sql select *  from spacex where LAUNCH_SITE like 'CCA%' limit 5;", "execution_count": null, "outputs": []}, {"metadata": {}, "id": "5f3f3a95-f842-453f-9aec-770976af841d", "cell_type": "markdown", "source": "### Task 3\n\n\n\n\n##### Display the total payload mass carried by boosters launched by NASA (CRS)\n"}, {"metadata": {}, "id": "701d7054-5983-4dad-93a1-8738139c7980", "cell_type": "code", "source": "%sql select sum(payload_mass__kg_)  from spacex where customer ='NASA (CRS)';", "execution_count": null, "outputs": []}, {"metadata": {}, "id": "0e14f297-4165-4d28-b82b-c4e502bc7ef3", "cell_type": "markdown", "source": "### Task 4\n\n\n\n\n##### Display average payload mass carried by booster version F9 v1.1\n"}, {"metadata": {}, "id": "5c9846c3-e5dc-4e72-b507-aa2592a054fa", "cell_type": "code", "source": "%sql select avg(payload_mass__kg_)  from spacex where booster_version ='F9 v1.1';", "execution_count": null, "outputs": []}, {"metadata": {}, "id": "a45072a9-7730-4b69-97ed-d0268811c564", "cell_type": "markdown", "source": "### Task 5\n\n##### List the date when the first successful landing outcome in ground pad was acheived.\n\n\n_Hint:Use min function_ \n"}, {"metadata": {}, "id": "17e67b40-6d7d-4558-b5e9-662fe281a6b6", "cell_type": "code", "source": "%sql select min(Date) from spacex where Landing__Outcome='Success (ground pad)'", "execution_count": null, "outputs": []}, {"metadata": {}, "id": "f4a27811-c954-4f53-a1e3-8590c09b761e", "cell_type": "markdown", "source": "### Task 6\n\n##### List the names of the boosters which have success in drone ship and have payload mass greater than 4000 but less than 6000\n"}, {"metadata": {}, "id": "ffd9b828-f248-4857-98cb-0ba01dd3d4c3", "cell_type": "code", "source": "%sql select booster_version from spacex where Landing__Outcome='Success (drone ship)' and  payload_mass__kg_ >4000 and   payload_mass__kg_ <6000", "execution_count": null, "outputs": []}, {"metadata": {}, "id": "64a3b1b5-efb7-4156-9918-28655779e419", "cell_type": "markdown", "source": "### Task 7\n\n\n\n\n##### List the total number of successful and failure mission outcomes\n"}, {"metadata": {}, "id": "6f541ead-8838-4db7-8f01-01577b69a76c", "cell_type": "code", "source": "%sql select substr(mission_outcome,1,7), count(*) from spacex group by mission_outcome", "execution_count": null, "outputs": []}, {"metadata": {}, "id": "3ae7619c-a8c7-409d-b85d-26ce81236e36", "cell_type": "markdown", "source": "### Task 8\n\n\n\n##### List the   names of the booster_versions which have carried the maximum payload mass. Use a subquery\n"}, {"metadata": {}, "id": "6d3ca7c3-923c-4609-804e-5a1450e19247", "cell_type": "code", "source": "% sql select booster_version from spacex where payload_mass__kg_ = (select max(payload_mass__kg_) from spacex)", "execution_count": null, "outputs": []}, {"metadata": {}, "id": "ed179e86-f977-4328-aad5-7be049fe8000", "cell_type": "markdown", "source": "### Task 9\n\n\n##### List the failed landing_outcomes in drone ship, their booster versions, and launch site names for in year 2015\n"}, {"metadata": {}, "id": "cedcbed9-4e5a-49fb-a18b-1c0d65696894", "cell_type": "code", "source": "% sql select  booster_version,launch_site,landing__outcome from spacex where year(date)= 2015 and landing__outcome='Failure (drone ship)'", "execution_count": null, "outputs": []}, {"metadata": {}, "id": "5f78519d-8725-411f-b42e-9d420d1c8b22", "cell_type": "markdown", "source": "### Task 10\n\n##### Rank the count of landing outcomes (such as Failure (drone ship) or Success (ground pad)) between the date 2010-06-04 and 2017-03-20, in descending order\n"}, {"metadata": {}, "id": "12d7b73d-26de-48c5-befd-0f24b12e1b91", "cell_type": "code", "source": "%sql select Landing__Outcome, count(*) from spacex where Date between '2010-06-04' and '2017-03-20' group by landing__outcome order by 2 desc", "execution_count": null, "outputs": []}, {"metadata": {}, "id": "e8d71a31-f2cc-49ba-99cd-1856c388b093", "cell_type": "markdown", "source": "### Reference Links\n\n* <a href =\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20String%20Patterns%20-%20Sorting%20-%20Grouping/instructional-labs.md.html?origin=www.coursera.org\">Hands-on Lab : String Patterns, Sorting and Grouping</a>  \n\n*  <a  href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Built-in%20functions%20/Hands-on_Lab__Built-in_Functions.md.html?origin=www.coursera.org\">Hands-on Lab: Built-in functions</a>\n\n*  <a  href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Labs_Coursera_V5/labs/Lab%20-%20Sub-queries%20and%20Nested%20SELECTs%20/instructional-labs.md.html?origin=www.coursera.org\">Hands-on Lab : Sub-queries and Nested SELECT Statements</a>\n\n*   <a href=\"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-3-SQLmagic.ipynb\">Hands-on Tutorial: Accessing Databases with SQL magic</a>\n\n*  <a href= \"https://cf-courses-data.s3.us.cloud-object-storage.appdomain.cloud/IBMDeveloperSkillsNetwork-DB0201EN-SkillsNetwork/labs/Module%205/DB0201EN-Week3-1-4-Analyzing.ipynb\">Hands-on Lab: Analyzing a real World Data Set</a>\n\n\n"}, {"metadata": {}, "id": "fa1521fd-7c86-4eff-8f64-b51e5131fbf8", "cell_type": "markdown", "source": "## Author(s)\n\n<h4> Lakshmi Holla </h4>\n"}, {"metadata": {}, "id": "572ec773-b1e0-413e-9397-c67c5894fc74", "cell_type": "markdown", "source": "## Other Contributors\n\n<h4> Rav Ahuja </h4>\n"}, {"metadata": {}, "id": "7f4005d1-e800-4f7b-b002-9a578c750c15", "cell_type": "markdown", "source": "## Change log\n| Date | Version | Changed by | Change Description |\n|------|--------|--------|---------|\n| 2021-10-12 | 0.4 |Lakshmi Holla | Changed markdown|\n| 2021-08-24 | 0.3 |Lakshmi Holla | Added library update|\n| 2021-07-09 | 0.2 |Lakshmi Holla | Changes made in magic sql|\n| 2021-05-20 | 0.1 |Lakshmi Holla | Created Initial Version |\n"}, {"metadata": {}, "id": "6a2bdd2d-d066-4ad5-9313-5e53830cd9f8", "cell_type": "markdown", "source": "## <h3 align=\"center\"> \u00a9 IBM Corporation 2021. All rights reserved. <h3/>\n"}], "metadata": {"kernelspec": {"name": "python3", "display_name": "Python 3.10", "language": "python"}, "language_info": {"name": "python", "version": "3.10.9", "mimetype": "text/x-python", "codemirror_mode": {"name": "ipython", "version": 3}, "pygments_lexer": "ipython3", "nbconvert_exporter": "python", "file_extension": ".py"}}, "nbformat": 4, "nbformat_minor": 4}